package com.lswms.modules.lswms.dao.statistics;

import java.util.List;
import java.util.Map;

import com.lswms.modules.lswms.entity.recipient.RecipientsEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;

@Mapper
public interface StatisticsDao  {

	@Select(value =" select a.sku,b.descr,b.effectlife,b.warningdays ,sum(qty) qty "
			+ " from lswms_inventory a join lswms_sku b  on a.sku = b.sku "
			+ " join lswms_lot c  on  a.lot = c.lot  "
			+ "	and ((c.lot1 is not null  and c.lot1 + b.effectlife-warningdays < GETDATE()  )  or ( c.lot2 is not null  and  c.lot2-warningdays < GETDATE() )) "
			+ " ${ew.customSqlSegment}"
			)
	List<Map<String, Object>> getEarlyWarning(@Param(Constants.WRAPPER)QueryWrapper wrapper);

	@Select(value="select a.sku,b.descr,b.safetyqty ,sum(qty) qty "
			+ "from lswms_inventory a join lswms_sku b  on  a.sku = b.sku  "
			+ "	join  lswms_lot c  on  a.lot = c.lot and c.lot10 <>1 "
		
			+"  ${ew.customSqlSegment} ")
	List<Map<String, Object>> getSafetyStock(@Param(Constants.WRAPPER)QueryWrapper wrapper);

	@Select(value="select a.cardId , d.recipient,d.recipientId,  e.listname  recipienttype , a.sku,c.descr,c.factorycdoe,isnull(c.price,0) price,icode.listname lot9,\n"
			+ "	convert(nvarchar(7),recipienttime,23) recipienttime , sum(qty) qty, sum(isnull(b.backqty,0)) backqty  , sum(isnull(b.damegeqty,0)) damegeqty\n"
			+ "from lswms_Recipients a   left join  (\n"
			+ "	select recipientkey , sum( case when  lot10 <>1 then backqty else 0 end )  backqty , sum( case when  lot10 =1 then backqty else 0 end )  damegeqty\n"
			+ "	from lswms_Recipientsback\n"
			+ "	group by recipientkey\n"
			+ " ) b   on a.recipientkey = b.recipientkey \n"
			+ " join  lswms_sku c on a.sku = c.sku \n"
			+ " LEFT join lswms_RecipientsInfo d on a.cardId = d.cardId\n"
			+ " left join lswms_codelist e on a.recipienttype = e.list and e.code='RECIPIENTTYPE' "
			+ "left join lswms_lot ilot on a.lot = ilot.lot "
			+"left join lswms_codelist icode on ilot.lot9 = icode.list and icode.code = 'NEWANDOLD'"
			+"  ${ew.customSqlSegment} "
			
			)
	List<Map<String, Object>> getRecipientReport(@Param(Constants.WRAPPER)QueryWrapper wrapper);

	@Select(value="select a.cardId , d.recipient,d.recipientId,  e.listname  recipienttype , a.sku,c.descr,c.factorycdoe,isnull(c.price,0) price,icode.listname lot9,\n"
			+ "	convert(nvarchar(7),recipienttime,23) recipienttime , sum(qty) qty, sum(isnull(b.backqty,0)) backqty  , sum(isnull(b.damegeqty,0)) damegeqty\n"
			+ "from lswms_Recipients a   left join  (\n"
			+ "	select recipientkey , sum( case when  lot10 <>1 then backqty else 0 end )  backqty , sum( case when  lot10 =1 then backqty else 0 end )  damegeqty\n"
			+ "	from lswms_Recipientsback\n"
			+ "	group by recipientkey\n"
			+ " ) b   on a.recipientkey = b.recipientkey \n"
			+ " join  lswms_sku c on a.sku = c.sku \n"
			+ " LEFT join lswms_RecipientsInfo d on a.cardId = d.cardId\n"
			+ " left join lswms_codelist e on a.recipienttype = e.list and e.code='RECIPIENTTYPE' "
			+ "left join lswms_lot ilot on a.lot = ilot.lot "
			+"left join lswms_codelist icode on ilot.lot9 = icode.list and icode.code = 'NEWANDOLD'"
			+"  ${ew.customSqlSegment} "

	)
	List<Map<String,Object>> getRecipientReport2(@Param(Constants.WRAPPER)QueryWrapper wrapper);

}
